<?php
class Search_Model_Selensen extends Search_Model_Base
{
	/**
	* 沿線一覧データ取得
	*
	* @access	public
	* @param	array
	* @return	void
	*/
	public function getEnsenData($params)
	{
		$db_params = array();
		$sql = $this->generateEnsenSql($params,$db_params);
		return $this->dbGetList($sql,$db_params);
	}

	/**
	* 沿線一覧データ取得（都道府県別）
	*
	* @access	public
	* @param	array
	* @return	void
	*/
	public function getEnsenDataByMulti($params)
	{
		$db_params = array();
		$sql = $this->generateEnsenSqlByMulti($params,$db_params);
		
		return $this->dbGetList($sql,$db_params);
	}

	/**
	* 沿線一覧データ取得（リクエスト用）
	*
	* @access	public
	* @param	array
	* @return	void
	*/
	public function getRequestEnsenDataByMulti($params)
	{
		$db_params = array();
		$sql = $this->generateRequestEnsenSqlByMulti($params,$db_params);
		return $this->dbGetList($sql,$db_params);
	}

	/**
	* 沿線一覧データ取得(バス・その他の交通機関を除く)
	*
	* @access	public
	* @param	array
	* @return	void
	*/
	public function getEnsenDataExceptBusSonota($params)
	{
		$db_params = array();
		$sql = $this->generateEnsenSqlExceptBusSonota($params,$db_params);
		return $this->dbGetList($sql,$db_params);
	}

	/**
	* 沿線コードから沿線名取得
	*/
	public function getRailNameByRailCd($params)
    {
        $db_params = array();
        $sql = $this->generateRailNameByRailCdSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 路線コードから路線集計データを取得
     * */
    public function getRailDataByRailCd($pref, $ensens)
    {
    	$db_params = array();
    	$sql = $this->generateEnsenSqlByCds($pref, $ensens);
    	return $this->dbGetList($sql, $db_params);
    }
    
	/**
	* 沿線一覧データを表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertEnsenData(&$data)
	{
		$data['Mapping'] = array();

		foreach ($data['selensen'] as $railData)
		{
			$ensenkubun = $railData['M02_ENSEN_KUBUN'];

			// が異なる場合
			if (array_key_exists($ensenkubun, $data['Mapping']) == false)
			{
				switch($railData['M02_ENSEN_KUBUN']) {
					case 1:
						$data['Mapping'][$ensenkubun]['ENSEN_KUBUN'] = "JR線";
						break;
					case 2:
						$data['Mapping'][$ensenkubun]['ENSEN_KUBUN'] = "私鉄";
						break;
					case 3:
						$data['Mapping'][$ensenkubun]['ENSEN_KUBUN'] = "地下鉄";
						break;
					case 4:
						$data['Mapping'][$ensenkubun]['ENSEN_KUBUN'] = "その他";
						break;
					case 5:
						$data['Mapping'][$ensenkubun]['ENSEN_KUBUN'] = "バス";
						break;
					case 9:
						$data['Mapping'][$ensenkubun]['ENSEN_KUBUN'] = "その他の交通機関";
						break;
				}
				$data['Mapping'][$ensenkubun][] = array();
			}

			$data['Mapping'][$ensenkubun]['ENSEN_DATA'][] = array(
			'T57_ENSEN_SHIKUCHOUSON_NAME'		=> $railData['T57_ENSEN_SHIKUCHOUSON_NAME'],
			'T57_ENSEN_SHIKUCHOUSON_CD'			=> $railData['T57_ENSEN_SHIKUCHOUSON_CD'],
			'M02_ENSEN_KANA'                    => $railData['M02_ENSEN_KANA'],
			'DATACNT'							=> $railData['DATACNT']
			);
		}
	}
	/**
	* 沿線データ検索用SQL文の取得(バス・その他の交通機関を除く)
	*
	* 沿線一覧データ検索用SQL文の取得を行う
	* @access	public
	* @param	array
	* @param	array
	* @return	string 沿線データ検索用SQL
	*/
	public function generateEnsenSql($params,$db_params)
	{
		$sql  = "";
		$sql .= "SELECT";
		$sql .= "       T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "      ,M02_ENSEN_KUBUN";
		$sql .= "      ,M02_ENSEN_KANA";
		$sql .= "      ,to_single_byte(T57_ENSEN_SHIKUCHOUSON_NAME) T57_ENSEN_SHIKUCHOUSON_NAME";
		$sql .= "      ,SUM(T57_ENSEN_SHIKUCHOUSON_CNT) AS DATACNT";
		$sql .= "   FROM";
		$sql .= "       M02ENSEN";
		$sql .= "      ,T57ENSEN_SHIKUCHOUSON_SUM";
		$sql .= "   WHERE";
		$sql .=         $this->createWhere("T57_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "       AND T57_ENSEN_SHIKUCHOUSON_FLAG = 0";
		if(!empty($params['SrhKbn']) && $params['SrhKbn'] == Apm_Config_Search::SRHKBN_SYATAKU){
			$sql .= "   AND T57_DATA_KUBUN = 2 ";
		}else{
			$sql .= "   AND T57_DATA_KUBUN = 0 ";
		}
		$sql .= "       AND T57_ENSEN_SHIKUCHOUSON_CD = M02_ENSEN_CD";
		$sql .= "   GROUP BY";
		$sql .= "       T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "      ,M02_ENSEN_KUBUN";
		$sql .= "      ,M02_ENSEN_KANA";
		$sql .= "      ,T57_ENSEN_SHIKUCHOUSON_NAME";
		$sql .= "   ORDER BY";
		$sql .= "       M02_ENSEN_KUBUN";
		$sql .= "      ,M02_ENSEN_CD";

        return $sql;
    }

	/**
	* 沿線一覧データを表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertEnsenDataByMulti(&$data)
	{
		$data['Mapping'] = array();

		$indexNo = 0;
        $beforePrefCd = null;
        $beforeEnsenKbn = null;
		$ensenKbnName = null;

		foreach ($data['selensen'] as  $railData)
		{
			$ensenkubun = $railData['M02_ENSEN_KUBUN'];
		    $prefCd = $railData['TODOUFUKEN_CD'];

            // 都道府県が異なる場合
            if ($beforePrefCd != $prefCd)
            {
            	$data['Mapping']["$prefCd"]['PrefName'] = $railData['TODOUFUKEN_NAME'];
            	$data['Mapping']["$prefCd"]['PrefEnName'] = Apm_Config_Search::$TODOUFUKEN_NAME_CODE_LIST[$prefCd];
                $data['Mapping']["$prefCd"]['EnsenKbnList'] = array();
                $beforeEnsenKbn = null;
            }

			// 沿線区分が異なる場合
			//if (array_key_exists($ensenkubun, $data['Mapping']) == false)
			if ($beforeEnsenKbn != $ensenkubun)
			{
				switch($railData['M02_ENSEN_KUBUN']) {
					case 1:
						$ensenKbnName = "JR線";
						break;
					case 2:
						$ensenKbnName = "私鉄";
						break;
					case 3:
						$ensenKbnName = "地下鉄";
						break;
					case 4:
						$ensenKbnName = "その他";
						break;
					case 5:
						$ensenKbnName = "バス";
						break;
					case 9:
						$ensenKbnName = "その他の交通機関";
						break;
				}

				$data['Mapping']["$prefCd"]['EnsenKbnList'][] = array(
                    'EnsenKbn'     => $ensenkubun,
            		'EnsenKbnName' => $ensenKbnName,
                    'RailList'     => array()
                );
                $indexNo = count($data['Mapping']["$prefCd"]['EnsenKbnList']) - 1;
			}

			$data['Mapping']["$prefCd"]['EnsenKbnList'][$indexNo]['RailList'][] = $railData;
			/*
			$data['Mapping']["$prefCd"]['ENSEN_DATA'][$indexNo]['RailList'][] = array(
			'T57_ENSEN_SHIKUCHOUSON_NAME'		=> $railData['T57_ENSEN_SHIKUCHOUSON_NAME'],
			'T57_ENSEN_SHIKUCHOUSON_CD'			=> $railData['T57_ENSEN_SHIKUCHOUSON_CD'],
			'M02_ENSEN_KANA'                    => $railData['M02_ENSEN_KANA'],
			'DATACNT'							=> $railData['DATACNT']
			);*/

			$beforePrefCd = $prefCd;
			$beforeEnsenKbn = $ensenkubun;
		}
	}

	/**
	* 沿線データ検索用SQL文の取得（都道府県別対応）
	*
	* 沿線一覧データ検索用SQL文の取得を行う
	* @access	public
	* @param	array
	* @param	array
	* @return	string 沿線データ検索用SQL
	*/
	public function generateEnsenSqlByMulti($params,&$db_params)
	{
		//バインド変数のセット	
		$whereSql = ""; 		//都道府県CDの条件を格納する
		$tmpSql = "";			//都道府県CDの条件を計算するために使用
		$index = 0;				 //都道府県CDの数
		//データ区分
		if(!empty($params['SrhKbn']) && $params['SrhKbn'] == Apm_Config_Search::SRHKBN_SYATAKU){
			$db_params['dataKubun'] = 2;
		}else{
			$db_params['dataKubun'] = 0;
		}		
		
        $sql  = "";
		$sql .= "SELECT /*+ INDEX(SUM IDX_T57ENSEN_SUM) */ ";
		$sql .= "       T57_TODOUFUKEN_CD AS TODOUFUKEN_CD ";
		$sql .= "      ,T57_TODOUFUKEN AS TODOUFUKEN_NAME ";
		$sql .= "      ,T57_ENSEN_CD AS T57_ENSEN_SHIKUCHOUSON_CD ";
		$sql .= "      ,T57_ENSEN_KUBUN AS M02_ENSEN_KUBUN ";
		$sql .= "      ,T57_ENSEN_KANA AS M02_ENSEN_KANA ";
		$sql .= "      ,to_single_byte(T57_ENSEN) AS ENSEN_NAME";
		$sql .= "      ,T57_ENSEN_CNT AS DATACNT";
		$sql .= "  FROM";
		$sql .= "      T57ENSEN_SUM SUM ";
		$sql .= " WHERE 1 = 1";
		
		if(isset($params['PrefCd']) && !empty($params['PrefCd'])){

			$sql .= "	AND T57_TODOUFUKEN_CD IN ". Apm_SqlUtil::generateWhereIn("PrefCD",$params['PrefCd'],$db_params);
			//$sql .=         $this->createWhereFromPrefParam("T57_TODOUFUKEN_CD",$params['PrefCd'],$db_params);
		}
		
		//★★★乗り換える条件があれば→条件として検索
		$transferCd = $params['transferCd'];
		if(isset($transferCd) && $transferCd != ''){
		    $sql .= "   AND T57_ENSEN_CD IN (SELECT DISTINCT M03_ENSEN_CD FROM M03EKI WHERE M03_RENRAKU_ENSEN_CD IN(".$transferCd.") AND T57_ENSEN_CD != ".$params['ensenCd']." )";
		}
		//★★★乗り換える条件があれば→条件として検索
		$sql .= "   	AND :dataKubun = T57_DATA_KUBUN(+) ";
		$sql .= "       AND (T57_ENSEN_KUBUN in (5,9) AND T57_ENSEN_CNT > 0";
		$sql .= "         OR T57_ENSEN_KUBUN not in (5,9) )";
		/*
		$sql .= " GROUP BY";
		$sql .= "       T57_TODOUFUKEN_CD";
		$sql .= "      ,T57_TODOUFUKEN";
		$sql .= "      ,T57_ENSEN_CD";
		$sql .= "      ,T57_ENSEN_KUBUN";
		$sql .= "      ,T57_ENSEN_KANA";
		$sql .= "      ,T57_ENSEN";
		*/
		$sql .= " ORDER BY";
		$sql .= "       T57_TODOUFUKEN_CD ";
		$sql .= "      ,T57_ENSEN_KUBUN";
		$sql .= "      ,T57_ENSEN_CD";
		//$sql .= "      ,M02_ENSEN_KANA";
		return $sql;
    }
    
    /**
     * 沿線データ検索用SQL文の取得（都道府県別対応）
     *
     * 沿線一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 沿線データ検索用SQL
     */
    public function generateEnsenSqlByCds($pref,$ensens)
    {
    
    	$sql  = "";
    	$sql .= "SELECT T57_TODOUFUKEN_CD AS TODOUFUKEN_CD ";
    	$sql .= "      ,T57_TODOUFUKEN AS TODOUFUKEN_NAME ";
    	$sql .= "      ,T57_ENSEN_CD AS T57_ENSEN_SHIKUCHOUSON_CD ";
    	$sql .= "      ,T57_ENSEN_KUBUN AS M02_ENSEN_KUBUN ";
    	$sql .= "      ,T57_ENSEN_KANA AS M02_ENSEN_KANA ";
    	$sql .= "      ,to_single_byte(T57_ENSEN) AS ENSEN_NAME";
    	$sql .= "      ,T57_ENSEN_CNT AS DATACNT";
    	$sql .= "  FROM";
    	$sql .= "      T57ENSEN_SUM SUM ";
    	$sql .= " WHERE";
    	$sql .= "		T57_TODOUFUKEN_CD IN (".$pref.")";
    	$sql .= "   AND T57_ENSEN_CD IN (".$ensens.")";
    	
    	$sql .= "   	AND 0 = T57_DATA_KUBUN(+) ";
    	$sql .= "       AND (T57_ENSEN_KUBUN in (5,9) AND T57_ENSEN_CNT > 0";
    	$sql .= "         OR T57_ENSEN_KUBUN not in (5,9) )";    	
    	$sql .= " ORDER BY";
    	$sql .= "       T57_TODOUFUKEN_CD ";
    	$sql .= "      ,T57_ENSEN_KUBUN";
    	$sql .= "      ,T57_ENSEN_CD";
    	return $sql;
    }

	/**
	* 沿線データ検索用SQL文の取得（リクエスト用）
	*
	* 沿線一覧データ検索用SQL文の取得を行う
	* @access	public
	* @param	array
	* @param	array
	* @return	string 沿線データ検索用SQL
	*/
	public function generateRequestEnsenSqlByMulti($params, &$db_params)
	{
        $sql  = "";
		$sql .= "SELECT";
		$sql .= "       M43_TODOUFUKEN_CD AS TODOUFUKEN_CD ";
		$sql .= "      ,M43_TODOUFUKEN AS TODOUFUKEN_NAME ";
		$sql .= "      ,M02_ENSEN_CD AS T57_ENSEN_SHIKUCHOUSON_CD ";
		$sql .= "      ,M02_ENSEN_KUBUN ";
		$sql .= "      ,M02_ENSEN_KANA ";
		$sql .= "      ,to_single_byte(M02_ENSEN_NAME) AS ENSEN_NAME";
		$sql .= "      ,-1 AS DATACNT";
		$sql .= "  FROM";
		$sql .= "       M02ENSEN";
		$sql .= "      ,M04ENSEN_AREA";
		$sql .= "      ,M43TODOUFUKEN";
		$sql .= " WHERE";
		$sql .=         $this->createWhereFromPrefParam("M04_TODOUFUKEN_CD",$params['PrefCd'],$db_params);
		$sql .= "       AND M04_ENSEN_CD = M02_ENSEN_CD";
		$sql .= "       AND M04_TODOUFUKEN_CD = M43_TODOUFUKEN_CD";
		$sql .= " ORDER BY";
		$sql .= "       M43_TODOUFUKEN_CD ";
		$sql .= "      ,M02_ENSEN_KUBUN";
		$sql .= "      ,M02_ENSEN_CD";
		//$sql .= "      ,M02_ENSEN_KANA";
        return $sql;
    }

	/**
	* 沿線データ検索用SQL文の取得(バス・その他の交通機関を除く)
	*
	* 沿線一覧データ検索用SQL文の取得を行う
	* @access	public
	* @param	array
	* @param	array
	* @return	string 沿線データ検索用SQL
	*/
	public function generateEnsenSqlExceptBusSonota($params,&$db_params)
	{ 

		//バインド変数のセット	
		// データ区分
		if(!empty($params['SrhKbn']) && $params['SrhKbn'] == Apm_Config_Search::SRHKBN_SYATAKU){
			$db_params['dataKubun'] = 2;
		}else{
			$db_params['dataKubun'] = 0;
		}		
	
		$sql  = "";
		$sql .= "SELECT";
		$sql .= "       T57_ENSEN_CD AS T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "      ,T57_ENSEN_KUBUN AS M02_ENSEN_KUBUN";
		$sql .= "      ,T57_ENSEN_KANA AS M02_ENSEN_KANA";
		$sql .= "      ,T57_ENSEN_CD AS M02_ENSEN_CD";
		$sql .= "      ,to_single_byte(T57_ENSEN) T57_ENSEN_SHIKUCHOUSON_NAME";
		$sql .= "      ,T57_ENSEN_CNT AS DATACNT";
		$sql .= "   FROM";
		$sql .= "      T57ENSEN_SUM";
		$sql .= "   WHERE";
		$sql .= "		T57_TODOUFUKEN_CD IN ". Apm_SqlUtil::generateWhereIn("PrefCD",$params['PrefCd'],$db_params);		
		//$sql .=         $this->createWhere("T57_TODOUFUKEN_CD",$params['PrefCd']);		
		$sql .= "   	AND T57_DATA_KUBUN = :dataKubun ";
		$sql .= "       AND T57_ENSEN_KUBUN NOT IN('5','9')";
		$sql .= "       AND T57_ENSEN_CNT > 0 ";		
		/*
		$sql .= "   GROUP BY";
		$sql .= "       T57_ENSEN_CD";
		$sql .= "      ,T57_ENSEN_KUBUN";
		$sql .= "      ,T57_ENSEN_KANA";
		$sql .= "      ,T57_ENSEN";
		*/
		$sql .= "   ORDER BY";
		$sql .= "       T57_ENSEN_KUBUN";
		$sql .= "      ,T57_ENSEN_CD";

        return $sql;
    }

    /**
	* 沿線データ検索用SQL文の取得(OEM)
	*
	* 沿線一覧データ検索用SQL文の取得を行う(OEM)
	* @access	public
	* @param	array
	* @param	array
	* @return
	*/
	public function generateOemSql($params,$db_params)
	{
		// OEMコードが8桁の場合,店舗検索
		// その他の場合、法人用検索
		$oemWhere = "";
		if(mb_strlen($params['OemCd'][0]) == 8){
			$oemWhere = $this->createWhere("T03_TENPO_CD",$params['OemCd']);
			$oemWhere .=" AND (M05_OEM = 2 OR M05_OEM = 5)";
		}else{
			$oemWhere = $this->createWhere("M05_HOUZIN_CD",$params['OemCd']);
			$oemWhere .=" AND (M05_OEM = 1 OR M05_OEM = 5)";
		}

		$sql = "";
		$sql .= "SELECT";
		$sql .= "       A.M02_ENSEN_CD AS T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "      ,to_single_byte(A.M02_ENSEN_NAME) AS T57_ENSEN_SHIKUCHOUSON_NAME";
		$sql .= "      ,A.M02_ENSEN_KANA AS M02_ENSEN_KANA";
		$sql .= "      ,NVL(A.M02_ENSEN_KUBUN, '6') AS M02_ENSEN_KUBUN";
		$sql .= "      ,SUM(C.REC_CNT) AS DATACNT";
		$sql .= "   FROM";
		$sql .= "       M02ENSEN A";
		$sql .= "      ,(";
		$sql .= "          SELECT";
		$sql .= "                DISTINCT M03_TODOUFUKEN_CD";
		$sql .= "               ,M03_ENSEN_CD";
		$sql .= "             FROM";
		$sql .= "                M03EKI";
		$sql .= "             WHERE";
		$sql .=                  $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "             GROUP BY";
		$sql .= "                M03_TODOUFUKEN_CD";
		$sql .= "               ,M03_ENSEN_CD";
		$sql .= "       ) B";
		$sql .= "      ,(";
		$sql .= "          SELECT";
		$sql .= "                M03_ENSEN_CD AS T03_ENSEN_CD";
		$sql .= "               ,SUM(REC_CNT) AS REC_CNT";
		$sql .= "             FROM";
		$sql .= "                M03EKI";
		$sql .= "               ,(";
		$sql .= "                  SELECT";
		$sql .= "                        DISTINCT M03_EKI_CD AS TGT_EKI_CD";
		$sql .= "                       ,M05_TENPO_CD";
		$sql .= "                       ,REC_CNT";
		$sql .= "                     FROM";
		$sql .= "                        M05TENPO_INFO";
		$sql .= "                       ,M03EKI";
		$sql .= "                       ,(";
		$sql .= "                          SELECT";
		$sql .= "                                T63_RENRAKU_CD AS TGT_RENRAKU_CD";
		$sql .= "                               ,T63_TENPO_CD AS TGT_TENPO_CD";
		$sql .= "                               ,SUM(T63_COUNT) AS REC_CNT";
		$sql .= "                             FROM";
		$sql .= "                                T63RENRAKU_EKI_SUM";
		$sql .= "                             WHERE";
		$sql .= "                                T63_TENPO_CD IS NOT NULL";
		$sql .= "                                AND T63_DATA_KUBUN = 0";
		$sql .= "                             GROUP BY";
		$sql .= "                                T63_RENRAKU_CD";
		$sql .= "                               ,T63_TENPO_CD";
		$sql .= "                        )";
		$sql .= "                     WHERE";
		$sql .=                          $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "                        AND";
		$sql .=                          $this->createWhere("M05_TENPO_CD",$params['PrefCd']);
		$sql .= "                        AND (";
		$sql .= "                              M05_OEM = 2";
		$sql .= "                           OR M05_OEM = 3";
		$sql .= "                            )";
		$sql .= "                        AND M03_RENRAKU_EKI_CD = TGT_RENRAKU_CD";
		$sql .= "                        AND M05_TENPO_CD = TGT_TENPO_CD";
		$sql .= "                )";
		$sql .= "             WHERE";
		$sql .= "                M03_EKI_CD = TGT_EKI_CD";
		$sql .= "             GROUP BY";
		$sql .= "                    M03_ENSEN_CD";
		$sql .= "          UNION ALL";
		$sql .= "          SELECT";
		$sql .= "                T03_ENSEN_CD01 AS T03_ENSEN_CD";
		$sql .= "               ,COUNT(*) AS REC_CNT";
		$sql .= "             FROM";
		$sql .= "                 T03WEB_HEYA";
		$sql .= "                ,M05TENPO_INFO";
		$sql .= "             WHERE";
		$sql .=                  $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "                AND";
		$sql .=                  $this->createWhere("M05_TENPO_CD",$params['PrefCd']);
		$sql .= "                AND (";
		$sql .= "                      M05_OEM = 2";
		$sql .= "                   OR M05_OEM = 3";
		$sql .= "                    )";
		$sql .= "                AND T03_RENRAKU_EKI_CD01 IS NULL";
		$sql .= "                AND M05_TENPO_CD = T03_TENPO_CD";
		$sql .= "             GROUP BY";
		$sql .= "                T03_ENSEN_CD01";
		$sql .= "          UNION ALL";
		$sql .= "          SELECT";
		$sql .= "                T03_ENSEN_CD02 AS T03_ENSEN_CD";
		$sql .= "               ,COUNT(*) AS REC_CNT";
		$sql .= "             FROM";
		$sql .= "                T03WEB_HEYA";
		$sql .= "               ,M05TENPO_INFO";
		$sql .= "             WHERE";
		$sql .=                  $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "                AND";
		$sql .=                  $this->createWhere("M05_TENPO_CD",$params['PrefCd']);
		$sql .= "                AND (";
		$sql .= "                      M05_OEM = 2";
		$sql .= "                   OR M05_OEM = 3";
		$sql .= "                    )";
		$sql .= "                AND T03_RENRAKU_EKI_CD02 IS NULL";
		$sql .= "                AND M05_TENPO_CD = T03_TENPO_CD";
		$sql .= "             GROUP BY";
		$sql .= "                T03_ENSEN_CD02";
		$sql .= "          UNION ALL";
		$sql .= "          SELECT";
		$sql .= "                T03_ENSEN_CD03 AS T03_ENSEN_CD";
		$sql .= "               ,COUNT(*) AS REC_CNT";
		$sql .= "             FROM";
		$sql .= "                T03WEB_HEYA";
		$sql .= "               ,M05TENPO_INFO";
		$sql .= "             WHERE";
		$sql .=                  $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "                AND";
		$sql .=                  $this->createWhere("M05_TENPO_CD",$params['PrefCd']);
		$sql .= "                AND (";
		$sql .= "                      M05_OEM = 2";
		$sql .= "                   OR M05_OEM = 3";
		$sql .= "                    )";
		$sql .= "                AND T03_RENRAKU_EKI_CD03 IS NULL";
		$sql .= "                AND M05_TENPO_CD = T03_TENPO_CD";
		$sql .= "             GROUP BY";
		$sql .= "                T03_ENSEN_CD03";
		$sql .= "       ) C";
		$sql .= "   WHERE";
		$sql .= "       A.M02_ENSEN_CD = C.T03_ENSEN_CD";
		$sql .= "       AND B.M03_ENSEN_CD = C.T03_ENSEN_CD";
		$sql .= "   GROUP BY";
		$sql .= "       A.M02_ENSEN_CD";
		$sql .= "      ,A.M02_ENSEN_KUBUN";
		$sql .= "      ,A.M02_ENSEN_NAME";
		$sql .= "      ,A.M02_ENSEN_KANA";
		$sql .= "   ORDER BY";
		$sql .= "       A.M02_ENSEN_KUBUN";
		$sql .= "      ,A.M02_ENSEN_CD";

		return $sql;
	}
	/**
	* 沿線データ検索用SQL文の取得(物件リクエスト)
	*
	* 沿線一覧データ検索用SQL文の取得を行う(物件リクエスト)
	* @access	public
	* @param	array
	* @param	array
	* @return
	*/
	public function generateRequestSql($params,$db_params)
	{
		$sql  = "";
		$sql .= "SELECT";
		$sql .= "      M02_ENSEN_CD AS T57_ENSEN_SHIKUCHOUSON_CD";
		$sql .= "     ,to_single_byte(M02_ENSEN_NAME) AS T57_ENSEN_SHIKUCHOUSON_NAME";
		$sql .= "     ,NVL(M02_ENSEN_KUBUN, '6') AS M02_ENSEN_KUBUN";
		$sql .= "     ,0 AS DATACNT";
		$sql .= "     ,M02_ENSEN_KANA";
		$sql .= "   FROM";
		$sql .= "      M02ENSEN";
		$sql .= "     ,(";
		$sql .= "        SELECT";
		$sql .= "              DISTINCT M03_TODOUFUKEN_CD";
		$sql .= "             ,M03_ENSEN_CD";
		$sql .= "           FROM";
		$sql .= "              M03EKI";
		$sql .= "           WHERE";
		$sql .=                $this->createWhere("M03_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "           GROUP BY";
		$sql .= "              M03_TODOUFUKEN_CD";
		$sql .= "             ,M03_ENSEN_CD";
		$sql .= "      ) B";
		$sql .= "  WHERE";
		$sql .= "      M02_ENSEN_CD = B.M03_ENSEN_CD";
		$sql .= "  GROUP BY";
		$sql .= "      M02_ENSEN_CD";
		$sql .= "     ,M02_ENSEN_NAME";
		$sql .= "     ,M02_ENSEN_KUBUN";
		$sql .= "     ,M02_ENSEN_KANA";
		$sql .= "  ORDER BY";
		$sql .= "      M02_ENSEN_KUBUN ASC";
		$sql .= "     ,M02_ENSEN_CD ASC";

		return $sql;
    }

   /**
     * 沿線名取得用SQL文の取得
     *
     * 沿線名用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	array 沿線名取得用SQL
     */
    public function generateRailNameByRailCdSql($params, &$db_params)
    {
		foreach($params['RailCd'] as $key => $val){
    		$db_params['railCd' . $key] = $val;
    	}

    	$sql = "";
		$sql .= "SELECT ";
		$sql .= "    distinct M03_ENSEN_CD ";
		$sql .= "    ,to_single_byte(M03_ENSEN_NAME) M03_ENSEN_NAME ";
		$sql .= "    ,M02_ENSEN_ALIAS ";
		$sql .= "FROM ";
		$sql .= "    M03EKI ";
		$sql .= "   ,M02ENSEN ";
		$sql .= "WHERE ";
		$sql .= "        M03_ENSEN_CD IN ( ";
		$sepa = "";
		foreach($params['RailCd'] as $key => $val){
			$sql .= $sepa . " :railCd" . $key;
			$sepa = ",";
		}
		$sql .= "        ) ";
		$sql .= "  AND M03_ENSEN_CD = M02_ENSEN_CD ";
		$sql .= "ORDER BY ";
		$sql .= "        M03_ENSEN_CD ";

        return $sql;
    }

	/**
	* Where句作成
	*
	* $paramsに格納されたデータをORorAND条件で連結する。
	*
	* @access	public
	* @param	string	カラム名
	* @param	array	対象項目データ
	* @param	array	DBパラメータ
	* @param	string	連結条件(デフォルト:OR)
	* @param	string	その他条件(デフォルト:なし)
	* @return	string Where句
	*/
	public function createWhereFromPrefParam($colums, $params, &$db_params, $option="OR", $plus="")
	{
		$strWhere = "";
		// $paramに格納されたデータ数分ループ
		for($i=0;$i<count($params);$i++)
		{
			// OR句の取得
			$strOr = ($i != 0) ? " ".$option : "";
			// パラメータ
			$db_params['strPrefCd_'.$i] = $params[$i];

			$strWhere .= $strOr." ( ".$colums." = :strPrefCd_".$i." ";
			$strWhere .= $plus . " ) ";

		}

		return " (" . $strWhere . ") ";
	}
	// 20141220 S_Add
	/**
	 * エリア検索（左パネル）のキーワードから、沿線を取得
	 * @param unknown_type $params
	 */
	public function getSearchEnsen($params)
	{
		$sql  = "";
		$sql .= "SELECT";
		$sql .= "       M43_TODOUFUKEN_CD AS TODOUFUKEN_CD ";
		$sql .= "      ,M43_TODOUFUKEN AS TODOUFUKEN_NAME ";
		$sql .= "      ,M02_ENSEN_CD AS T57_ENSEN_SHIKUCHOUSON_CD ";
		$sql .= "      ,M02_ENSEN_KUBUN ";
		$sql .= "      ,M02_ENSEN_KANA ";
		$sql .= "      ,to_single_byte(M02_ENSEN_NAME) AS ENSEN_NAME";
		$sql .= "      ,-1 AS DATACNT";
		$sql .= "  FROM";
		$sql .= "       M02ENSEN";
		$sql .= "      ,M04ENSEN_AREA";
		$sql .= "      ,M43TODOUFUKEN";
		$sql .= " WHERE";
		$sql .= "       M04_TODOUFUKEN_CD = ";
		$sql .= "'" . $params['prefcd'] . "' ";
		$sql .= "       AND M04_ENSEN_CD = M02_ENSEN_CD";
		$sql .= "       AND M04_TODOUFUKEN_CD = M43_TODOUFUKEN_CD";
		$sql .= " ORDER BY";
		$sql .= "       M43_TODOUFUKEN_CD ";
		$sql .= "      ,M02_ENSEN_KUBUN";
		$sql .= "      ,M02_ENSEN_CD";
		//$sql .= "      ,M02_ENSEN_KANA";
		
		return $this->dbGetList($sql, $db_params);
	}
	// 20141220 E_Add
	public function getEnsenSetsumei($code){
		$db_params = array();
		$db_params['strLineCd'] = $code;
		$sql = "SELECT M02_SETSUMEI_HEADLINE FROM M02ENSEN_SETSUMEI WHERE M02_ENSEN_CD = :strLineCd AND rownum = 1";
		
		return $this->dbGetList($sql, $db_params); 
	}
	
}
?>
